import numpy as np
import pymysql
import pandas as pd
import pymysql.cursors

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            database='scenic',
            port=3306,
            charset='utf8'
        )

    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT o.tourist_agency_name, u.id_no, IFNULL(u.id_no, 2) as province_code,
        SUBSTRING(u.id_no, 7, 4) as birth_year
        FROM ticket_order_user_rel u JOIN ticket_order o ON u.order_id = o.id JOIN order_user_age_rel g ON pet_ticket_rel.id = u.id
        JOIN tourist_agency g ON g.id = u.agency_id
        WHERE o.order_time >= '2024-01-01' AND o.order_time <= '2024-12-31'
        """
        cursor.execute(sql)
        df = cursor.fetchall()
        cursor.close()
        df = pd.DataFrame(df)
        print(df)

        # 判断是否为周末
        df['non_weekend'] = df['non_weekend'].apply(lambda x:1 if x not in [1, 7] else 0)

        # 计算有效指标
        df['valid_id'] = df['id_no'].apply(lambda x: 1 if x and str(x).strip() != '' else 0)
        # 标记是否为老人、青年及中年游客
        df['elderly_ratio'] = df.apply(
            lambda x: 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        df['out_province_ratio'] = df.apply(
            lambda x: 1 if (x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else np.nan,
            axis=1
        )

        # 计算各省游客比例
        result = df.groupby(['tourist_agency_name']).agg({
            'valid_id': ['sum', 'count'],
            'elderly_ratio': ['sum', 'count'],
            'out_province_ratio': ['sum', 'count']
        }).reset_index()

        # 计算各省老人、青年及中年游客比例
        result['out_province_ratio'] = result['out_province_ratio'] / result['valid_id'].replace(0, np.nan)
        result['elderly_ratio'] = result['elderly_ratio'] / result['valid_id'].replace(0, np.nan)

        # 删除无效列
        result = result.drop(['out_province', 'elderly'], axis=1)
        result['out_province_ratio'] = result['out_province_ratio'].fillna(0)
        result['elderly_ratio'] = result['elderly_ratio'].fillna(0)
        print(result.head())
        result.to.csv('scenic data.csv')

if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()